<?php
/**
* エラーチェッククラス
**/
require_once( '../system/Class_M_DAO.php' );
class Class_T_MON_CELL extends Class_M_DAO {
	//protected properties begin
	/**
	 *
	 * テーブル名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_table = "T_MON_CELL";
	
	/**
	 *
	 * 主キー名
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	protected $_pkey = "id";
	
	/**
	 *
	 * ソート順
	 *
	 * @access    protected
	 * @var       string
	 *
	 */
	 
	 protected $_tab_last_id = "";
	 
	// protected $_order = "id";
	 
	 protected $_cols     = array(
	 	"shop_cd"           => array(AOKI_VARTYPE_STR),
		"matrix_cd"         => array(AOKI_VARTYPE_STR),
	 );
	
	 protected $_checks  = array(
		"shop_cd"     => array( "EXIST" ),
		"matrix_cd"   => array( "EXIST" )
		
	 );
	 
	 //maxlength 
	 protected $_maxlens  = array(
		"shop_cd"      =>   10 ,
		"matrix_cd "   =>   10 
	 );
	
	public function get_matrix_chk_fields() {
		$sea_chk_fields  = array(
			"year4"           => array( "EXIST"),
			"week_cd"         => array( "EXIST")		
		);
	 return $sea_chk_fields;
	}
	
	function setTableNameWeek($initid = "" ){
		$this->_tab_last_id = $initid;
		//$this->_table       = $this->_table.$initid;
	}
	public function getMatrixTerm($value) {
		$sql = "SELECT a.*,CURRENT_TIMESTAMP as sysd,b.u_time as upd_time FROM M_MATRIX_TERM a " .
				" inner join T_MON_CELL_WEEK b on a.matrix_cd = b.matrix_cd WHERE a.matrix_cd = '{$value}'";
		$this->_db->query( $sql );
		if( $this->_db->num_rows() ){
			return $this->_db->fetch();
		}else{
			return  null;
		}
	}
	
	public function getMonCellSaveTimeMsg($matrix_cd, $aja_cd = null) {
		$sql = "SELECT a.*,(select  user_name FROM M_LOGIN where id  = a.u_user) as m_user_name  FROM T_MON_CELL a ";
		
		if (!empty( $aja_cd )) {
	 		$sql = $sql ." inner join M_AJAREKS aja  on aja.shop_cd = a.shop_cd  and aja.userid = {$aja_cd}";
	 	}
	 	
	 	$sql = $sql ." WHERE a.matrix_cd = '{$matrix_cd}'";
	 	
		$this->_db->query( $sql );
		$data = array();
		while( $row = $this->_db->fetch() ){
			$data[$row["shop_cd"]] = $row;
		}
		return $data;
	}
	
	public function getMapGroupShop($matrix_cd, $params = null) {
	/*
		$sql ="select a.shop_cd ,c.shop_name, c.sale, a.mark,a.group_no, 
		max(IF(b.sort='1',a.congruent_ins, '')) as congruent_ins, 
		max(IF(b.sort='1',a.map_no, '')) as m1, 
		max(IF(b.sort='2',a.map_no, '')) as m2, 
		max(IF(b.sort='4',a.map_no, '')) as m4, 
		max(IF(b.sort='5',a.map_no, '')) as m5, 
		max(IF(b.sort='3' and b.media_cd = c.lnews1,a.map_no, '')) as l1,
		max(IF(b.sort='3' and b.media_cd = c.lnews2,a.map_no, '')) as l2,
		max(IF(b.sort='3' and b.media_cd = c.lnews3,a.map_no, '')) as l3, 
		max(IF(b.sort='3' and b.media_cd = c.lnews4,a.map_no, '')) as l4, 
		max(IF(b.sort='3' and b.media_cd = c.lnews5,a.map_no, '')) as l5, 
		e.user_name,e.login_id,f.user_name as zuser_name,f.login_id as zlogin_id 
		from  M_SHOP c
		inner join M_MAP_GROUP_SHOP a  on a.shop_cd = c.shop_cd 
		inner join M_MAP_GROUP b on a.map_no = b.map_no 
		inner join M_AJAREKS d on d.shop_cd = a.shop_cd 
		inner join M_LOGIN e on e.id = d.userid and e.auth = 3 
		left join M_LOGIN f on f.id = d.userid and e.auth = 2
		inner join M_AJAREKS aja on aja.shop_cd = a.shop_cd and aja.userid = 547 
		where a.matrix_cd = '2012010' group by a.shop_cd  ";
		*/
		/*
		$sql = "select a.shop_cd,a.shop_name " .
				" from M_SHOP a " .
				" inner join  M_AJAREKS d  on d.shop_cd = a.shop_cd " ;
		*/
		$sql =  "select a.shop_cd ,c.shop_name, a.mark,a.congruent_ins,a.ins_load_ratio as ratio,a.group_no,c.sale," .
				"cf.plane_cost,cf.plane_qua, " .
	 		" max(IF(b.sort='1',a.map_no, '')) as r, max(IF(b.sort='2',a.map_no, '')) as i," .
	 		" max(IF(b.sort='4',a.map_no, '')) as m4, max(IF(b.sort='5',a.map_no, '')) as m5," .
	 		
	 		//" max(IF(b.sort='3' and b.media_cd = c.lnews1,a.map_no, '')) as lnews1," .
	 		//" max(IF(b.sort='3' and b.media_cd = c.lnews2,a.map_no, '')) as lnews2," .
	 		//" max(IF(b.sort='3' and b.media_cd = c.lnews3,a.map_no, '')) as lnews3," .
	 		//" max(IF(b.sort='3' and b.media_cd = c.lnews4,a.map_no, '')) as lnews4," .
	 		//" max(IF(b.sort='3' and b.media_cd = c.lnews5,a.map_no, '')) as lnews5," .
	 		" e.user_name,e.login_id,f.user_name as zuser_name " .
	 		" from  M_MAP_GROUP_SHOP a" .
	 		" inner join M_MAP_GROUP b  on a.map_no = b.map_no " .
	 		" inner join M_SHOP c  on a.shop_cd = c.shop_cd" .
	 		" inner join M_LOGIN  e  on e.id = c.area_aja".
	 		" inner join M_LOGIN  f  on f.id = c.zone_aja".
	 		" left  join M_COST_FLIER  cf  on cf.shop_cd = a.shop_cd  and  cf.matrix_cd = {$matrix_cd}";
	 		
	 	/*
	 	if (!empty( $aja_cd )) {
	 		$sql = $sql ." inner join M_AJAREKS aja on aja.shop_cd = a.shop_cd  and aja.userid = {$aja_cd}";
	 	}
	 	*/

	 	$whereshopcd = "";
	 	if (!empty( $params )) {
	 		if  ( !empty( $params['shop_cd']) ) {
				$shop_cd = $params["shop_cd"] ;
				$whereshopcd = " and  a.shop_cd = '{$shop_cd}'";
			}
	 		if  ( !empty( $params['zone_aja']) ) {
				
				$whereshopcd = " and  c.zone_aja = '{$params['zone_aja']}'";
			}
	 		if  ( !empty( $params['area_aja']) ) {
				
				$whereshopcd = " and  c.area_aja = '{$params['area_aja']}'";
			}
			if  ( !empty( $params['title']) ) {
				$title = $params['title'];
				$sql = $sql ." inner join (select ff.shop_cd from T_MON_CELLD_F ff where ff.matrix_cd = '{$matrix_cd}' and ff.title_cd = '{$title}'  group by ff.shop_cd) f on f.shop_cd = a.shop_cd" ;
			}
			
			if  ( ( !empty( $params['upd_f']) ) ||  ( !empty( $params['upd_t']) ) ) {
				$whereupd = array();
				if ( !empty( $params['upd_f']) ) {
					$whereupd[] = Class_PWD::get_u_time_where_sql( $params['upd_f'], 1, 'uf') ;
				}
				$now_matrix_cd = $params["matrix_cd"] ;
				if ( !empty( $params['upd_t']) ) {
					$whereupd[] = Class_PWD::get_u_time_where_sql( $params['upd_t'], 2, 'uf') ;
				}
				$whereupdsql = implode(" and ",$whereupd );				
				$sql = $sql ." inner join (select uf.shop_cd from T_MON_CELL uf where uf.matrix_cd = '{$now_matrix_cd}' and {$whereupdsql} ) u on u.shop_cd = a.shop_cd";
				
			}
	 	}
	 	
	 	$sql = $sql ." where a.matrix_cd = '{$matrix_cd}'  {$whereshopcd} group by a.shop_cd ";
	 		
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[$row["shop_cd"]] = $row;
			}
			return $data;
			
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	
	public function getCellDetails($matrix_cd_f, $is_final = FALSE, $aja_cd = NULL, $params = NULL) {
		$whereshopcd  = "" ;
		$joinsql   = "" ;
	 	if (!empty( $params )) {
	 		if  ( !empty( $params['shop_cd']) ) {
				$shop_cd = $params["shop_cd"] ;
				$whereshopcd = " and  a.shop_cd = '{$shop_cd}'";
			}
			
			if  ( !empty( $params['pattern_cd']) ) {
				$pattern_cd = $params["pattern_cd"] ;
				$whereshopcd = " and  a.pattern_cd = '{$pattern_cd}'";
			}
			
			if  ( !empty( $params['title_cd']) ) {
				$title_cd    = $params["title_cd"] ;
				$whereshopcd = " and  a.title_cd = '{$title_cd}'";
			}
			
	 		if  ( !empty( $params['title']) ) {
				$title         = $params["title"] ;
				$now_matrix_cd = $params["matrix_cd"] ;
				$joinsql = " inner join (select ff.shop_cd from T_MON_CELLD_F ff where ff.matrix_cd = '{$now_matrix_cd}' and ff.title_cd = '{$title}' group by ff.shop_cd) f on f.shop_cd = a.shop_cd" ;
			}
			if  ( ( !empty( $params['upd_f']) ) ||  ( !empty( $params['upd_t']) ) ) {
				$whereupd = array();
				if ( !empty( $params['upd_f']) ) {
					$whereupd[] = Class_PWD::get_u_time_where_sql( $params['upd_f'], 1, 'uf') ;
				}
				$now_matrix_cd = $params["matrix_cd"] ;
				if ( !empty( $params['upd_t']) ) {
					$whereupd[] = Class_PWD::get_u_time_where_sql( $params['upd_t'], 2, 'uf') ;
				}
				$whereupdsql = implode(" and ",$whereupd );				
				$joinsql = $joinsql." inner join (select uf.shop_cd from T_MON_CELL uf where uf.matrix_cd = '{$now_matrix_cd}' and {$whereupdsql} ) u on u.shop_cd = a.shop_cd";
			
			}
	 	}
	 	$t_mon_cell_detail_table = "T_MON_CELLD_D" ;
		if ($is_final) {
			$t_mon_cell_detail_table = "T_MON_CELLD_F" ;
		}
		
		$sql =  "select * from {$t_mon_cell_detail_table} a" ;
		
	 	if (!empty( $aja_cd )) {
			$sql =  $sql." INNER JOIN M_SHOP c  on c.shop_cd = a.shop_cd and ( c.zone_aja = {$aja_cd} or c.area_aja  = {$aja_cd} )" ;
		} 
		
		$sql =  $sql.$joinsql ;
		$sql = $sql." where a.matrix_cd = '{$matrix_cd_f}' {$whereshopcd}  order by a.shop_cd ";
		
		try {
			$this->_db->query( $sql );
			$data = array();
			if ($is_final) {
				while( $row = $this->_db->fetch() ){
					if ( $row["delupd"] != '1' ) {
						$data[] = $row;
					}
				}
			} else {
				while( $row = $this->_db->fetch() ){
					$data[] = $row;
				}
			}
			return $data;
			
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	
	//印刷明細
	public function getPrnCellDetails($matrix_cd, $is_final = FALSE, $aja_cd = null, $newyear_flag = NULL) {
		$t_mon_cell_detail_table = "T_MON_CELLD_D" ;
		if ($is_final) {
			$t_mon_cell_detail_table = "T_MON_CELLD_F" ;
		}
		$tpattern = 
		$fromA = "( select mat.flier_date,c.shop_name,
			 mgs.shop_cd, mgs.mark ,mgs.map_no,mgs.seq,
			mav.size,mav.mano_out,mav.mano_in,mav.contractor,
			tm.title_cd,tm.title" ;
		if ( $newyear_flag=='1' ) {
			$fromA = $fromA. ",ys.year_mark ";
		}else {
			$fromA = $fromA. " '' as year_mark ";
		}
		$ajawhere = "";
		if ($aja_cd ){
			$ajawhere =  " where (zone_aja = {$aja_cd} or area_aja = {$aja_cd}) ";
		}
		
		$fromA = $fromA." from  M_MAP_GROUP_SHOP  mgs
			 inner join  M_MATRIX_TERM  mat on mgs.matrix_cd = mat.matrix_cd 
			 inner join ( select shop_cd,shop_name from M_SHOP {$ajawhere} union 
			 select mservice_cd as shop_cd, mservice_name as shop_name from  M_MAP_SERVICE  ) c on mgs.shop_cd = c.shop_cd 
			 inner join (select title_cd,title,map_no from {$t_mon_cell_detail_table} group by title_cd,title,map_no) tm on mgs.map_no = tm.map_no  
			 inner join M_MATRIX_VER  mav on mgs.matrix_cd = mav.matrix_cd and tm.title_cd = mav.id" ;
		
		if ( $newyear_flag=='1' ) {
			$fromA = $fromA." LEFT JOIN M_NEWYEAR_SHOP ys  on ys.shop_cd = mgs.shop_cd  " ;
		}
		
		$fromA = $fromA." where mgs.matrix_cd = '{$matrix_cd}'  order by mgs.map_no ) a ";
			
		$fromB = " (select a.title_cd,a.shop_cd,a.size as psize,dev.pnums as pnums, a.map_no" ;
		if ($is_final) {
			$fromB = $fromB. ",a.verupd,a.numupd,a.addupd,a.delupd";
		}
		/*
		$where_ins_co="";
		if ($ins_co){
			$where_ins_co = " and ins.ins_co_cd ='{$ins_co}' ";
		} 
		*/
		$fromB = $fromB.",mp.pattern_name,dev.media_name,dev.delive_name,dev.close_day,dev.close_time,
				 tmc.u_time,tmc.c_time,(select  user_name FROM M_LOGIN where id  = tmc.u_user) as m_user_name 
				 from {$t_mon_cell_detail_table} a 
	 			 inner join  T_MON_CELL  tmc on a.matrix_cd = tmc.matrix_cd and a.shop_cd = tmc.shop_cd  
	 			
	 			 inner join (select ins_c.shop_cd,ins.media_name,ins.delive_name,del.close_day,del.close_time,tpat.pnums 
	 			 from   M_INS_SHOP_SHOP{$matrix_cd} ins_c 
	 			 inner join  M_INS_SHOP ins  on ins.ins_shop_cd = ins_c.ins_shop_cd
	 			 inner join  T_PATTERN{$matrix_cd} tpat   on tpat.ins_shop_shop_cd = ins_c.ins_shop_shop_cd
	 			 inner join  M_DELIVE del  on del.delive_cd = ins.delive_cd and del.matrix_cd ={$matrix_cd}
	 			 group by  ins_c.shop_cd,ins.media_name,del.delive_cd )  dev  on dev.shop_cd =a.shop_cd 
	 			
	 			 inner join  M_PATTERN mp  on mp.pattern_cd = a.pattern_cd 
	 			 where a.matrix_cd = '{$matrix_cd}' ) b" ;
	  //SQL
	 	$sql =  "select  a.flier_date, a.shop_cd, a.shop_name, a.mark, a.map_no, 
	 			a.title_cd,a.title,b.shop_cd as shop_cd2,b.psize, b.pnums, 
	 			a.contractor,a.size, a.mano_out, a.mano_in,a.year_mark,
	 			b.pattern_name, b.media_name, b.delive_name, b.close_day,b.close_time,
	 			b.u_time,b.c_time,b.m_user_name " ;
	 		
	 	if ($is_final) {
			$sql = $sql. ", b.verupd, b.numupd, b.addupd, b.delupd";
		}
	 	$sql = $sql." from ".$fromA ." inner join ". $fromB. " on a.shop_cd = b.shop_cd  and a.title_cd = b.title_cd and a.map_no = b.map_no " ;
	 	$sql = $sql." order by a.title_cd, a.map_no,a.seq, a.shop_cd,b.media_name,b.delive_name ";
	 	
		/*
		$sql =  "select a.title_cd,a.title,a.shop_cd,a.size as psize, a.pnums , " .
				" c.shop_name, '99' as opentime  ," .
				"  mav.contractor,mat.flier_date,mav.size,mav.mano_out,mav.mano_in," .
				"  mgs.mark ,mgs.map_no, " .
				"  mp.pattern_name,ins.media_name,ins.delive_name," .
				"  mat.flier_date as close_date,del.close_time, " .
				"  tmc.u_time,tmc.c_time,(select  user_name FROM M_LOGIN where id  = tmc.u_user) as m_user_name " ;
		if ($is_final) {
			$sql = $sql. ",a.verupd,a.numupd,a.addupd,a.delupd";
		}
		if ( $newyear_flag=='1' ) {
			$sql = $sql. ",ys.year_mark ";
		}
		$sql = $sql.
				" from  M_MAP_GROUP_SHOP  mgs".
				" inner join  M_MATRIX_TERM  mat on a.matrix_cd = mat.matrix_cd  " .
	 			" inner join  M_MATRIX_VER  mav on a.matrix_cd = mav.matrix_cd and a.title_cd = mav.id " .
	 			
	 			" inner join {$t_mon_cell_detail_table} a on a.shop_cd = mgs.shop_cd and a.matrix_cd = mgs.matrix_cd " .
	 			" inner join  T_MON_CELL  tmc on a.matrix_cd = tmc.matrix_cd and a.shop_cd = tmc.shop_cd  " .
	 			" inner join  M_SHOP c  on a.shop_cd = c.shop_cd " .
	 			" inner join  M_INS_SHOP_SHOP ins_c  on ins_c.shop_cd = c.shop_cd " .
	 			" inner join  M_INS_SHOP ins  on ins.ins_shop_cd = ins_c.ins_shop_cd " .
	 			" inner join  M_DELIVE del  on del.delive_cd = ins.delive_cd " .
	 			" inner join  M_PATTERN mp  on mp.pattern_cd = a.pattern_cd " ;
	 			
	 	if (!empty( $aja_cd )) {
			$sql =  $sql." INNER JOIN M_AJAREKS c  on c.shop_cd = a.shop_cd and c.userid = '{$aja_cd}' " ;
		} 
		if ( $newyear_flag=='1' ) {
			$sql =  $sql." LEFT JOIN M_NEWYEAR_SHOP ys  on ys.shop_cd = a.shop_cd  " ;
		}
		$sql = $sql." where mgs.matrix_cd = '{$matrix_cd}'  order by a.title_cd,mgs.map_no,a.shop_cd,ins.media_name,ins.delive_name ";
		*/
		try {
			$this->_db->query( $sql );
			$data = array();
			if ($is_final) {
				while( $row = $this->_db->fetch() ){
					if ( $row["delupd"] != '1' ) {
						$data[] = $row;
					}
				}
			} else {
				while( $row = $this->_db->fetch() ){
					$data[] = $row;
				}
			}
			return $data;
			
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	
	public function getCellDetailsAll( $matrix_cd_f, $matrix_cd_t , $is_final = FALSE, $aja_cd = null, $shops = null ) {
		$t_mon_cell_detail_table = "T_MON_CELLD_D" ;
		
		if ($is_final) {
			$t_mon_cell_detail_table = "T_MON_CELLD_F" ;
		}
		
		$sql =  " select a.matrix_cd ,  a.qtype , a.wtype ,a.shop_cd, a.title_cd , a.title , a.size , a.map_no ,
		 a.pattern_cd ,p.pattern_name,  a.pnums , a.sample,
		 v.mano_out,v.mano_in,v.exp_ins_week
		 from {$t_mon_cell_detail_table} a
		INNER JOIN M_MATRIX_VER v  on v.id = a.title_cd 
		INNER JOIN M_PATTERN p  on p.pattern_cd = a.pattern_cd " ;		
		$canJoinShop = false; 
		$whereJoinShop = "";
	 	if (!empty( $aja_cd )) {
	 		$canJoinShop = true; 
			$whereJoinShop  ="  and (c.zone_aja = {$aja_cd} or c.area_aja = {$aja_cd} ) " ;
		}
		if (!empty( $shops )) {
			$canJoinShop = true; 
			$whereJoinShop  =  $whereJoinShop  ."  and a.shop_cd IN {$shops} " ;
		}
		if ($canJoinShop){
			$sql =  $sql." INNER JOIN M_SHOP c  on c.shop_cd = a.shop_cd ". $whereJoinShop;
		}
		
		$size_arr = array(
			'B2'  => '1',
			'B3'  => '2',
			'B4'  => '3'
		);
		
		$sql = $sql." where a.matrix_cd >= '{$matrix_cd_f}'  and a.matrix_cd <= '{$matrix_cd_t}'  ";
		$sql = $sql." order by a.shop_cd,a.matrix_cd,a.qtype,a.wtype desc ";
		
		try {
			$this->_db->query( $sql );
			$data = array();
			$now_shop_cd = "";
			$now_matrix_cd = "";
			while( $row = $this->_db->fetch() ){
				if ( ( $now_shop_cd != $row["shop_cd"] ) || ($now_matrix_cd != $row["matrix_cd"] ) ){
					//$qi_index      = 1;
					$now_shop_cd   = $row["shop_cd"];
					$now_matrix_cd = $row["matrix_cd"];
				}
				
				if  ($row["qtype"] == '0') {
					$row["type"]        = "qi";
					$row["type_index"]  = 1 ;
					
					//$qi_index ++ ;
				} else {
					$row["type"]       = "lo";
					$row["type_index"] = $row["qtype"];
				}
				$row["size_index"] =  $size_arr[$row["size"]] ;
				
				$data[ $now_shop_cd ][] = $row;
			}
			return $data;
			
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	
	public function setMaxMinMatrixCD($year4, &$matrix_cd_f,&$matrix_cd_t) {
		if ( empty( $matrix_cd_f) ||  empty( $matrix_cd_t) ) {
			$sql =  "select  max(a.matrix_cd) as maxweek , min(a.matrix_cd) as minweek  from  M_MATRIX_TERM a  where  a.year4  = '{$year4}'" ;
			try {
				$this->_db->query( $sql );
				$data = array();
				if ( $row = $this->_db->fetch() ){
					$matrix_cd_f = $matrix_cd_f ? $matrix_cd_f: $row["minweek"];
					$matrix_cd_t = $matrix_cd_t ? $matrix_cd_f: $row["maxweek"];
				}else {
					$matrix_cd_f = $matrix_cd_f ? $matrix_cd_f: $year4."0101";
					$matrix_cd_t = $matrix_cd_t ? $matrix_cd_f: $year4."9999";
				}
				return true;
			} catch (Exception $e) {
				echo "例外キャッチ：", $e->getMessage(), "\n";
				return false;
			}
		}
		return true;
	}
	
	function getTitle( $value) {
		$sql =  "select a.id,a.groupid ,a.title, a.saletickt,a.size,a.area_id,a.area,a.pattern_cd,a.exp_ins_flg,a.exp_ins_week,a.map,a.memo " .
		" from  M_MATRIX_VER a ".
		" where a.matrix_cd = '{$value}' and a.user_flag = 1" ;
		/*
		if (empty($patterntype)){
			$sql = $sql. "and a.pattern_cd = 0 ";
		}
		if ($patterntype == 999  ){
			$sql = $sql. "and a.pattern_cd > 0 ";
		}
		*/
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	
	
	function getPatternNums( $aja_cd = NULL ,$weekid = NULL) {
		$tablefrom =  "T_PATTERN_D".$weekid;
		$sql =  " select b.shop_cd ,a.pattern_cd,sum(pnums) as nums from T_PATTERN_D a " .
		" INNER JOIN {$tablefrom} b  on a.ins_shop_shop_cd = b.ins_shop_shop_cd " ;
		//if (!empty( $aja_cd )) {
		//	$sql =  $sql." INNER JOIN M_AJAREKS c  on c.shop_cd = b.shop_cd where c.userid = '{$aja_cd}' " ;
		//}

		$sql = $sql." GROUP BY b.shop_cd ,a.pattern_cd";
		
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[] = $row;
			}
			return $data;
			
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
//$local = TRUE 地域　      $local = FALSE 通常チラシ　
	public function dosave($local, $values, $matrix_cd, $is_final = FALSE, $aja_cd =  NULL,$pattern =  NULL,$title_cd =  NULL ) {
		$inssql_baseF = "INSERT INTO T_MON_CELLD_F(shop_cd, matrix_cd, title_cd, title, size, map_no, isirgyu, qtype, wtype,  pattern_cd , pnums,sample,verupd,numupd, addupd, delupd, u_time, u_user, c_time, c_user) values ";
		$inssql_base  = "INSERT INTO T_MON_CELLD_D(shop_cd, matrix_cd, title_cd, title, size, map_no, isirgyu, qtype,wtype,  pattern_cd , pnums,sample,) values ";
		
		$db = $this->_db;	
		if (!$local){
			$this->begin_trans();
		}
		$noerror = TRUE;
		$draft_data = array();
		
		if ($is_final){
			$nowaja_cd =  NULL ;
			if ($aja_cd){
				$nowaja_cd = $aja_cd;
			}
			$params  =  NULL ;
			if ($pattern){
				$params["pattern_cd"] =  $pattern;
				$params["title_cd"]   =  $title_cd;
			}
			$draft_data1 = $this->getCellDetails($matrix_cd, FALSE, $nowaja_cd, $params);
			
			foreach ($draft_data1 as $k=>$v) {
				$keyid        = $v["shop_cd"]."_".$v["qtype"]."_".$v["wtype"]."_".$v["size"] ;
				$draft_data[$keyid] = $v;
			}
		}
		$loginuser = $_SESSION['ADMIN_LOGIN']['STAFF_SEQ'];
		if ($local){
			$where    = " matrix_cd = '{$matrix_cd}' and pattern_cd = $pattern and title_cd = $title_cd  ";
			$where    =  $where." AND qtype > 0 " ;	
			
			$delsql  = "DELETE FROM T_MON_CELLD_D WHERE ".$where;
			if (!$is_final) {
				if (!$db->query( $delsql )) {
					return   FALSE;
				
				}
			}
			
			$delsqlF = "DELETE FROM T_MON_CELLD_F WHERE ".$where;
			if (!$db->query( $delsqlF )) {
				return   FALSE;
			}
		}
		foreach ($values as $k=>$v) {
			$shop_cd  = $v["shop_cd"];
			
			$where    = " matrix_cd = '{$matrix_cd}' AND shop_cd = '{$shop_cd}' ";
			
			if ($this->exits($where)) {
				$sqlbase = "update T_MON_CELL set u_time = CURRENT_TIMESTAMP ,u_user = '{$loginuser}' where ". $where ;
			}else {
				$sqlbase = "insert into T_MON_CELL(shop_cd,matrix_cd,u_time,u_user,c_time,c_user) values('{$shop_cd}','{$matrix_cd}',";
				
				$sqlbase = $sqlbase."CURRENT_TIMESTAMP,'{$loginuser}' ".",CURRENT_TIMESTAMP,'{$loginuser}') ";
			}
			
			$where    =  $where." AND qtype = 0 " ;
			
			$innerjoin = "";
			if (!$local){
				$delsql  = "DELETE FROM T_MON_CELLD_D WHERE ".$where;
				$delsqlF = "DELETE FROM T_MON_CELLD_F WHERE ".$where;
			}
			$insVajues  = array();
			$insVajuesF = array();
			
			foreach ( $v["detail"] as $kk=>$vv ) {
				
				$pattern_cd       = $vv["pattern_cd"];
				$pnums            = $vv["pnums"];
				$wtype            = $vv["wtype"];
				$qtype            = $vv["qtype"];
				
				$title_cd         = $vv["title_cd"];
				$title            = $vv["title"];
				$size             = $vv["size"];
				
				$map_no           = $vv["map_no"];
				$isirgyu          = $vv["isirgyu"];
				$sample           = $vv["sample"];
				
				$verupd           = 0;
				$numupd           = 0;
				$addupd           = 0;
				$delupd           = 0;
				
				if ($is_final) {
					$keyid    = $shop_cd."_".$qtype."_".$wtype."_".$size ;
					if (isset($draft_data[$keyid])) {
						$draft_row = $draft_data[$keyid] ;
						if  ($draft_row["title"] != $title) {
							$verupd = 1;
						}
						if  ( $draft_row["pattern_cd"] != $pattern_cd || $draft_row["pnums"] != $pnums ) {
							$numupd = 1;
						}
						$draft_data[$keyid]["notdel"] = "1";
					}else {
						$addupd = 1;
					}
				} else {
					$insVajues[]   = "('{$shop_cd }','{$matrix_cd}','{$title_cd}','{$title}','{$size}','{$map_no}','{$isirgyu}','{$qtype}','{$wtype}','{$pattern_cd}',{$pnums},{$sample} )";
				}
				$insVajuesF[]  = "('{$shop_cd }','{$matrix_cd}','{$title_cd}','{$title}','{$size}','{$map_no}','{$isirgyu}','{$qtype}','{$wtype}','{$pattern_cd}',{$pnums},{$sample},'{$verupd}','{$numupd}','{$addupd}','{$delupd}',CURRENT_TIMESTAMP,'{$loginuser}' ".",CURRENT_TIMESTAMP,'{$loginuser}') ";
				
			}
			
			if (!$is_final) {
				$inssql = $inssql_base.implode (' , ',  $insVajues);
			}
			
			$inssqlF = $inssql_baseF.implode(' , ', $insVajuesF);
			
			try {
				if (!$db->query( $sqlbase )) {
					$noerror =  FALSE;
					break;
				}
				
				if (!$is_final) {
					if (!$local){
						if (!$db->query( $delsql )) {
							$noerror =  FALSE;
							break;
						}
					}
				}
				if (!$local){
					if (!$db->query( $delsqlF )) {
						$noerror =  FALSE;
						break;
					}
				}
				if (!$is_final) {
					if (!$db->query( $inssql )) {
						$noerror =  FALSE;
						break;
					}
				}
				
				if (!$db->query( $inssqlF )) {
					$noerror =  FALSE;
					break;
				}
				

			} catch (Exception $e) {
				echo "例外キャッチ：", $e->getMessage(), "\n";
					$noerror =  FALSE;
					break;
			}
		}
		
		if  ( !empty( $delValuesF ) ) {
			foreach ($draft_data as $dkey=>$dv) {
				if ( !isset($dv["notdel"]) ) {
					$shop_cd          = $dv["shop_cd"];
					$pattern_cd       = $dv["pattern_cd"];
					$pnums            = $dv["pnums"];
					$wtype            = $dv["wtype"];
					$qtype            = $dv["qtype"];
					$title_cd         = $dv["title_cd"];
					$title            = $dv["title"];
					$size             = $dv["size"];
					$map_no           = $dv["map_no"];
					$isirgyu          = $dv["isirgyu"];
					$verupd           = 0;
					$numupd           = 0;
					$addupd           = 0;
					$delupd           = 1;
					$delValuesF[]     = "('{$shop_cd }','{$matrix_cd}','{$title_cd}','{$title}','{$size}','{$map_no}','{$isirgyu}','{$qtype}','{$wtype}','{$pattern_cd}',{$pnums},'{$verupd}','{$numupd}','{$addupd}','{$delupd}',CURRENT_TIMESTAMP,'{$loginuser}' ".",CURRENT_TIMESTAMP,'{$loginuser}') ";
				}
			} 
			
			$inssqlF =  $inssql_baseF.implode(' , ', $delValuesF);
			try {
				if (!$db->query( $inssqlF )) {
					$noerror =  FALSE;
				}
			} catch (Exception $e) {
				echo "例外キャッチ：", $e->getMessage(), "\n";
				$noerror =  FALSE;
				break;
			}
		}
		
		if ( $noerror ){
			if (!$local){
				$this->commit_trans();
			}
			return true;
		} else {
			if (!$local){
				$this->rollback_trans();
			}
			return false;
		}
		
	}

	public  function get_cell_week($year, $please_select = NULL, $date_flg = FALSE, $cell =FALSE, $matrix_f = NULL,  $matrix_t = NULL){	
		$db = $this->_db;	
		$sql = " select a.week_cd,a.matrix_cd,a.week_seq,a.week_date_begin,a.flier_date from M_MATRIX_TERM  a ";
		if ($cell){
				$sql = $sql. " INNER JOIN T_MON_CELL_WEEK b on a.matrix_cd = b.matrix_cd ";
		}
		$sql = $sql. " where a.year4 = '{$year}' ";
		
		if  ( !empty( $matrix_f ) ) {
			$sql = $sql. " AND a.matrix_cd >= '{$matrix_f}' ";
		}
		if  ( !empty( $matrix_t ) ) {
			$sql = $sql. " AND a.matrix_cd <= '{$matrix_t}' ";
		}
		$sql = $sql." order by a.week_cd";
		$db->query( $sql );
		
		if ($please_select){
			$data[""] = $please_select;
		}
		
		while( $row = $db->fetch() ){
			$v = $row["week_cd"]."週";
			if ($row["week_seq"] != 0) {
				$v = $v.$row["week_seq"]."回";
			}
			if ($date_flg) {
				$v = $v." ".Class_PWD::int8_to_cell_dateD( $row["flier_date"] )."";
			}
			$row["disp_week"] = $v;
			$data[$row["matrix_cd"]] = $row;
		}
		
		return $data;
	}
	
	public function getshops($aja_cd=null, $shops = null) {
		$db = $this->_db;
		$sql = " select a.shop_cd,  a.shop_name, b.user_name as area_name, c.user_name as zone_name  from M_SHOP a  ";
		$sql = $sql." inner join    M_LOGIN  b on a.area_aja = b.id ";
		$sql = $sql." inner join    M_LOGIN  c on a.zone_aja = c.id ";
		$whereShop = "";
		if (!empty( $aja_cd )) {
			$whereShop = " where (a.zone_aja = {$aja_cd} or a.area_aja = {$aja_cd}) " ;
		}
		
		if (!empty( $shops )) {
			if (empty($whereShop)){
				$whereShop = " where a.shop_cd IN {$shops} " ;
			}else {
				$whereShop =  $whereShop." and a.shop_cd IN {$shops} " ;
			}
			
		}
		
		//inner join
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[$row["shop_cd"]] = $row;
			}
			return $data;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	
	public function getshopsRez( $matrix_cd_f = null, $matrix_cd_t = null, $aja_cd = null, $shops = null ) {
		$db = $this->_db;
		$sql = " select a.shop_cd,  rz.matrix_cd,  
		rz.now_quantity, rz.last_quantity,rz.diff_quantity,rz.rate_quantity,
		rz.now_sales, rz.last_sales,rz.diff_sales,rz.rate_sales, 
		rz.now_price, rz.last_price,rz.diff_price,rz.rate_price 
		from M_SHOP a 
		INNER JOIN M_SHOP_RHIZOME rz  on rz.shop_cd = a.shop_cd 
		 where rz.matrix_cd >= '{$matrix_cd_f}'  and rz.matrix_cd <= '{$matrix_cd_t}'" ; 
		
		if (!empty( $aja_cd )) {
			$sql =  $sql." and a.zone_aja = {$aja_cd} or a.area_aja = {$aja_cd} " ;
			
		}
		
		if (!empty( $shops )) {
			$sql =  $sql." and a.shop_cd IN {$shops} " ;
		}
		//inner join
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[$row["shop_cd"]][$row["matrix_cd"]] = $row;
			}
			return $data;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	
	public function getshopsCostLast( $matrix_cd_f = null, $matrix_cd_t = null, $aja_cd = null, $shops = null ) {
		$db = $this->_db;
		$sql = " select wk.shop_cd,mat.matrix_cd, wk.cell_cost 
		from M_SHOP a  
		inner join T_SHOP_WEEK wk  on wk.shop_cd = a.shop_cd 
		INNER JOIN M_MATRIX_TERM  mat  on mat.pre_matrix_cd = wk.matrix_cd
		where mat.matrix_cd >= '{$matrix_cd_f}'  and mat.matrix_cd <= '{$matrix_cd_t}'" ; 
		if (!empty( $aja_cd )) {
			$sql =  $sql." and a.zone_aja = {$aja_cd} or a.area_aja = {$aja_cd} " ;
		}
		if (!empty( $shops )) {
			$sql =  $sql." and a.shop_cd IN {$shops} " ;
		}
		//inner join
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[$row["shop_cd"]][$row["matrix_cd"]] = $row["cell_cost"];
			}
			return $data;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	
	public function getshopsDm($year4, $matrix_cd_f = null, $matrix_cd_t = null, $aja_cd = null, $shops = null ) {
		$db = $this->_db;
		$sql =" select dmc.matrix_cd, dmc.shop_cd, 
		  SUM(IF(dm.sort='1',dmc.count * dmp.teigata,0)) AS c1,max(IF(dm.sort='1',dm.title,'')) t1,
		  SUM(IF(dm.sort='2',dmc.count * dmp.vatutyaku,0)) AS c2,max(IF(dm.sort='2',dm.title,'')) t2,
		  SUM(IF(dm.sort='3',dmc.count * dmp.zatutyaku,0)) AS c3,max(IF(dm.sort='3',dm.title,'')) t3,
		  SUM(IF(dm.sort='4',dmc.count * dmp.fuusyo,0)) AS c4,max(IF(dm.sort='4',dm.title,'')) t4,
		  SUM(IF(dm.sort='5',dmc.count * dmp.tp,0)) AS c5,max(IF(dm.sort='5',dm.title,'')) t5
		  from M_COST_DM_COUNT dmc 
		  inner join M_SHOP a
		  inner join M_COST_DM dm on dm.id = dmc.dm_no 
		  inner join *M_COST_DM_PRICE* dmp on dmc.count >= dmp.num_from and  dmc.count <= dmp.num_to and  year4 =2012
		  where dmc.matrix_cd >= '{$matrix_cd_f}'  and dmc.matrix_cd <= '{$matrix_cd_t}'" ;
		if (!empty( $aja_cd )) {
			$sql =  $sql." and a.zone_aja = {$aja_cd} or a.area_aja = {$aja_cd} " ;
			
		}
		
		if (!empty( $shops )) {
			$sql =  $sql." and a.shop_cd IN {$shops} " ;
		}
		$sql =  $sql." group by  dmc.matrix_cd, dmc.shop_cd  order by  dmc.matrix_cd, dmc.shop_cd ";
		//inner join
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[$row["shop_cd"]][$row["matrix_cd"]] = $row;
			}
			return $data;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}

	public function getQrsPrice($year,  $aja_cd) {
		$db = $this->_db;
		$sql = "SELECT c.shop_cd,p.b2,p.b3,p.b4 from M_SHOP c " .
				" inner join (select shop_cd, sum(case size when 'B2' then price else 0 end) as 'b2', " .
				"sum(case size when 'B3' then price else 0 end) as 'b3', " .
				"sum(case size when 'B4' then price else 0 end) as 'b4' " .
				"from M_COST_FLIER_PRICE where year4='{$year}'  group by shop_cd ) p on p.shop_cd = c.shop_cd" ; 
					
		if (!empty( $aja_cd )) {
			$sql =  $sql." where c.zone_aja = {$aja_cd} or c.area_aja = {$aja_cd} " ;
		}
		$sql =  $sql." ORDER BY c.shop_cd ";
		//inner join
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[$row["shop_cd"]] = $row;
			}
			return $data;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	
	public function getQrsPriceShopSize($year,  $aja_cd,$shops) {
		$db = $this->_db;
		$sql = "SELECT a.shop_cd,a.size, a.price   from M_COST_FLIER_PRICE a ";
		
		if (!empty( $aja_cd )) {
			$sql =  $sql." inner join M_SHOP c on c.shop_cd = a.shop_cd and c.zone_aja = {$aja_cd} or c.area_aja = {$aja_cd} " ;
		}
		if (!empty( $aja_cd )) {
			$sql =  $sql." inner join M_SHOP c on c.shop_cd = a.shop_cd  and a.shop_cd IN {$shops}" ;
		}
		$sql =  $sql."  where a.year4='{$year}'  ORDER BY a.shop_cd ";
		//inner join
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[$row["shop_cd"]][$row["size"]] = $row["price"];
			}
			return $data;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	public function getAreaPatternPrice($year) {
		$db = $this->_db;
		$sql = "select pattern_cd,size,price from M_COST_FLIER_PRICE_AREA where year4={$year} " ;
		$sql =  $sql." ORDER BY pattern_cd ";
		//inner join
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[$row["pattern_cd"]][$row["size"]] = $row["price"];
			}
			return $data;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	public function getShopRhizome($matrix_cd) {
		$db = $this->_db;
		$sql = " SELECT  shop_cd,now_quantity,last_quantity from M_SHOP_RHIZOME where matrix_cd = {$matrix_cd}" ;
		//inner join
		$hasdata = false;
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$hasdata = true;
				$data[$row["shop_cd"]] = $row;
			}
			if (!$hasdata){
				$sql = " SELECT  shop_cd,now_quantity,last_quantity from M_SHOP_RHIZOME where matrix_cd = {select max(matrix_cd) from M_SHOP_RHIZOME )" ;
				$this->_db->query( $sql );
				while( $row = $this->_db->fetch() ){
					$data[$row["shop_cd"]] = $row;
				}
			}
			return $data;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	//select  shop_cd,cell_cost from  T_SHOP_WEEK where matrix_cd = {}
	public function getShopWeek($matrix_cd) {
		$db = $this->_db;
		$sql = "select  shop_cd,cell_cost from  T_SHOP_WEEK where matrix_cd = {$matrix_cd}" ;
		//inner join
		try {
			$this->_db->query( $sql );
			$data = array();
			while( $row = $this->_db->fetch() ){
				$data[$row["shop_cd"]] = $row;
			}
			return $data;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	
	public function getAjaxVer($titleid, $shop_cd){
		$db = $this->_db;
		$sql = " select *  from M_MATRIX_VER  where  id = {$titleid}";
		$data = array();
		try {
		$db->query( $sql );
		if( $row = $this->_db->fetch() ){
			return $row;
		}
			return $data;
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return NULL;
		}
	}
	
	public function delAreaTitle($where){
		$sql = "DELETE  FROM T_MON_CELLD_F WHERE {$where} ";
		try {
			if ($this->_db->query( $sql )) {
				return TRUE;
			} else {
				return FALSE;
			}
		} catch (Exception $e) {
			echo "例外キャッチ：", $e->getMessage(), "\n";
			return FALSE;
		}
	}

}

?>